﻿@page "/Form3"
@using System.Diagnostics.CodeAnalysis
@using BootstrapBlazor.OnlyServerZhongShun.Data.dbmodels
@using System.Data
@inject ILogger<Form3> NormalLogger
@inject AuthenticationStateProvider authenticationStateProvider
@inject ToastService ToastService
@inject IConfiguration _congifuration
@inject SqlSugarScope _SqlSugarScope

@attribute [Authorize(Roles = "业务总, 业务经理, 业务员,不含回款数据,超级管理员")]

<style>
    .multi-select {
        --bb-multi-select-min-height: 26px;
    }

    .checkbox-list {
        --bb-checkbox-item-width: 80px;
    }
    .radio-list {
        --bb-radio-item-width: 50px;
    }
</style>
<div class="row g-1">
    <div class="col-6">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询日期" />
            <DateTimeRange @bind-Value="@NormalDateTimeRangeValue" OnConfirm="OnNormalConfirm" ShowSidebar="true" ViewMode="DatePickerViewMode.Date" IsEditable="true" DateFormat="yyyy-MM-dd">
                <TimePickerSetting ShowClockScale="true" IsAutoSwitch="false" />
            </DateTimeRange>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="客户" />
            <Select IsVirtualize="true" Items="客户列表Items" @bind-Value="选中客户" OnSearchTextChanged="OnSearchTextChanged" ShowSearch="true" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="业务员" />
            <MultiSelect Items="@业务员Items" @bind-Value="@选中业务员List" OnSelectedItemsChanged="@业务员选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>
</div>
<div class="row g-1">
    <div class="col-md-4">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="部门" />
            <MultiSelect Items="@部门Items" @bind-Value="@选中部门List" OnSelectedItemsChanged="@部门选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>
    <div class="col-md-4">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="产品" />
            <MultiSelect Items="@产品Items" @bind-Value="@选中产品List" OnSelectedItemsChanged="@产品选择改变事件" ShowToolbar="true" ShowSearch="true" />
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="养殖类别" />
            <Select IsVirtualize="true" Items="养殖类别Items" @bind-Value="选中养殖类别" ShowSearch="false" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
</div>
<div class="row g-1">
    <div class="col-8">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询列" />
            <CheckboxList TValue="string" @bind-Value="@选中查询列" Items="@查询列" ShowLabel="false" ShowLabelTooltip="true"></CheckboxList>
        </BootstrapInputGroup>
    </div>
    <div class="col-2">
        <RadioList TValue="string" Items="@查询类别" @bind-Value="@选中查询类别" />
    </div>
    <div class="col-2">
        <Button OnClick="@Button_chaxun_Click" IsAsync="true" Color="Color.Primary">查询</Button>
        <Button OnClick="@Button_daochu" Color="Color.Info">导出</Button>

    </div>
</div>
    <Table TItem="DynamicObject" DynamicContext="DataTableDynamicContext"
           IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="false"
           OnExportAsync="OnExportAsync" OnSort="OnSort" ShowFooter="true" IsStriped="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" TableSize="TableSize.Compact"
    Height="440" IsFixedHeader="true" >
        <FooterTemplate>
            <tr>
                @if (DataTableDynamicContext != null && DataTableDynamicContext.DataTable.Rows.Count > 0)
                {
                    @foreach (DataColumn property in DataTableDynamicContext.DataTable.Columns)
                    {
                        <td>
                            @*  //如果不是 代理商名称,部门 列 *@
                            @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "差异率" && property.ColumnName != "本年退货率" && property.ColumnName != "往年退货率" && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        else if (property.ColumnName == "序号")
                        {
                            @:合计
                        }
                        else if (property.ColumnName == "差异率")
                        {
                            @* (sum(本年发货) - sum(往年发货)) / sum(往年发货) *@
                            var sum本年发货 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("本年净发货")) return 0m;
                                object value = dr["本年净发货"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            var sum往年发货 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("往年净发货")) return 0m;
                                object value = dr["往年净发货"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            if (sum往年发货 != 0)
                            {
                                @(((sum本年发货 - sum往年发货) / sum往年发货).ToString("p2"));
                            }
                        }
                        else if (property.ColumnName == "本年退货率")
                        {
                            @* (sum(本年发货) - sum(往年发货)) / sum(往年发货) *@
                            var sum本年退货 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("本年退货")) return 0m;
                                object value = dr["本年退货"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            var sum本年净发货 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("本年净发货")) return 0m;
                                object value = dr["本年净发货"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            if (sum本年净发货 != 0)
                            {
                                @((sum本年退货 / sum本年净发货).ToString("p2"))
                                ;
                            }
                        }
                        else if (property.ColumnName == "往年退货率")
                        {
                            @* (sum(本年发货) - sum(往年发货)) / sum(往年发货) *@
                            var sum往年退货 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("往年退货")) return 0m;
                                object value = dr["往年退货"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            var sum往年净发货 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("往年净发货")) return 0m;
                                object value = dr["往年净发货"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            if (sum往年净发货 != 0)
                            {
                                @((sum往年退货 / sum往年净发货).ToString("p2"))
                                ;
                            }
                        }
                        else
                        {
                            @:--
                        }

                        </td>
                    }
                }

            </tr>
        </FooterTemplate>
    </Table>

@code {
    private string username;

    [Inject]
    [NotNull]
    private ITableExport? TableExport { get; set; }
    [Inject]
    [NotNull]
    private ToastService? Toast { get; set; }

    private List<View_Custom> View_CustomList = new List<View_Custom>();
    private List<View_Depart> View_DepartList = new List<View_Depart>();
    private List<View_Inventory> View_InventoryList = new List<View_Inventory>();


    private DateTimeRangeValue NormalDateTimeRangeValue { get; set; } = new();



    private IEnumerable<SelectedItem> 客户列表Items => View_CustomList.Select(i => new SelectedItem(i.cCusCode!, i.cCusName!)).ToList();
    private IEnumerable<SelectedItem> 部门Items => View_DepartList.Select(i => new SelectedItem(i.cDepCode!, i.cDepName!)).ToList();
    private IEnumerable<SelectedItem> 产品Items => View_InventoryList.Select(i => new SelectedItem(i.cInvName!, i.cInvName!)).ToList();


    private SelectedItem? 选中客户 { get; set; }
    private IEnumerable<string> 选中部门List { get; set; }
    private string 选中部门InStr = "";

    private IEnumerable<string> 选中产品List { get; set; }
    private string 选中产品InStr = "";

    private List<View_CustomPerson> View_CustomPersonList = new List<View_CustomPerson>();
    private IEnumerable<string> 选中业务员List { get; set; }
    private string 选中业务员InStr = "";
    private IEnumerable<SelectedItem> 业务员Items => View_CustomPersonList.Select(i => new SelectedItem(i.cCusPPerson!, i.cPersonName!)).ToList();
    private List<View_Custom> view_Customs = new List<View_Custom>();
    private SelectedItem? 选中养殖类别 { get; set; }
    private IEnumerable<SelectedItem> 养殖类别Items => view_Customs.Select(i => new SelectedItem(i.养殖类别!, i.养殖类别!)).ToList();

    [NotNull]
    private IEnumerable<SelectedItem>? 查询列 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "客户", Value = "客户名称" },
        new SelectedItem { Text = "部门", Value = "部门"},
        new SelectedItem { Text = "业务员", Value = "业务员" },
        new SelectedItem { Text = "产品名称", Value = "产品名称" },
        new SelectedItem { Text = "类别", Value = "类别" },
        // new SelectedItem { Text = "金额", Value = "金额" },
        // new SelectedItem { Text = "数量", Value = "数量" }
    });

    [NotNull]
    private IEnumerable<SelectedItem>? 查询类别 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "金额", Value = "0" },
        new SelectedItem { Text = "数量", Value = "1"}
    });

    private string 选中查询列 { get; set; }
    private string 选中查询类别 { get; set; }

    [NotNull]
    private DataTableDynamicContext? DataTableDynamicContext { get; set; }

    protected override async void OnInitialized()
    {
        var authState = await authenticationStateProvider.GetAuthenticationStateAsync();
        var user = authState.User;
        username = user.Identity.Name;
        //今年1月1日
        NormalDateTimeRangeValue.Start = new DateTime(DateTime.Now.Year, 1, 1);
        //今天
        NormalDateTimeRangeValue.End = DateTime.Now.AddDays(-1);

        View_CustomList = _SqlSugarScope.Queryable<View_Custom>().ToList();
        //加一个请选择
        View_CustomList.Insert(0, new View_Custom { cCusCode = "", cCusName = "请选择" });

        try
        {
            List<View_Custom> cus = _SqlSugarScope.Queryable<View_Custom>().Where(it => !string.IsNullOrEmpty(it.养殖类别)).Distinct().Select(it => new View_Custom { 养殖类别 = it.养殖类别 }).ToList();
            view_Customs = cus;
            view_Customs.Insert(0, new View_Custom { 养殖类别 = "请选择" });
            view_Customs.Add(new View_Custom { 养殖类别 = "非小棚虾" });
            View_InventoryList = _SqlSugarScope.Queryable<View_Inventory>()
                                                .Where(it => it.cInvCode.StartsWith("03") || it.cInvCode.StartsWith("13") || it.cInvCode.StartsWith("20"))
                                                // .Where("exists (select 1 from View_发票明细 where View_发票明细.cInvCode = View_Inventory.cInvCode)")
                                                .ToList();
            View_InventoryList.Insert(0, new View_Inventory { cInvName = "请选择" });
        }
        catch (Exception)
        {


        }
        //读取配置文件
        UserDataAuth userDataAuth = _SqlSugarScope.Queryable<UserDataAuth>().Where(it => it.UserName == username).First();
        List<Guid> guids = userDataAuth.CanViewDepart.Split(",").ToList().Select(it => Guid.Parse(it)).ToList();
        List<Sys_Department> depts = _SqlSugarScope.Queryable<Sys_Department>().Where(it => guids.Contains(it.DepartmentId)).ToList();
        List<string> deptNames = depts.Select(it => it.DepartmentName).ToList();
        var PaiChuBuMen = _congifuration.GetSection("PaiChuBuMen").Value;
        List<View_Depart> deptss = _SqlSugarScope.Queryable<View_Depart>().Where($"cDepCode not in ({PaiChuBuMen})").ToList();
        View_DepartList = deptss.Where(it => deptNames.Contains(it.cDepName)).ToList();
        //读取业务员配置文件
        var PaiChuYeWuYuan = _congifuration.GetSection("PaiChuYeWuYuan").Value;
        string sql = $"select distinct cPersonName,cCusPPerson from View_CustomPerson where cCusPPerson not in ({PaiChuYeWuYuan})";
        View_CustomPersonList = _SqlSugarScope.Ado.SqlQuery<View_CustomPerson>(sql);

    }
    private Task OnNormalConfirm(DateTimeRangeValue value)
    {
        NormalLogger.LogDebug($"选择的时间范围是: {value.Start:yyyy-MM-dd HH:mm:ss} - {value.End:yyyy-MM-dd HH:mm:ss}");
        //遍历输出选中的客户
        if (选中客户 != null)
        {
            NormalLogger.LogDebug($"选中的客户是: {选中客户.Text}");
        }
        //遍历输出选中的部门
        if (选中部门List != null)
        {
            foreach (var item in 选中部门List)
            {

            }
        }
        return Task.CompletedTask;
    }
    private IEnumerable<SelectedItem> OnSearchTextChanged(string searchText)
    {
        return View_CustomList.Where(i => i.cCusName!.Contains(searchText, StringComparison.OrdinalIgnoreCase)).Select(i => new SelectedItem(i.cCusCode!, i.cCusName!));
    }

    private Task 部门选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中部门InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }
    private Task 产品选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中产品InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }
    private Task 业务员选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中业务员InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }
    private async Task<bool> OnExportAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), context.Columns, $"Test_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private async void Button_daochu(MouseEventArgs e)
    {
        //复制一个table用来导出
        DataTable dt = DataTableDynamicContext.DataTable.Copy();
        //增加一行合计
        DataRow dr = dt.NewRow();
        dr["本年净发货"] = dt.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("本年净发货")) return 0m;
                                    object value = dr["本年净发货"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });
        dr["往年净发货"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("往年净发货")) return 0m;
            object value = dr["往年净发货"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });
        dr["差异额"] = (decimal)dr["本年净发货"] - (decimal)dr["往年净发货"];
        dr["差异率"] = (decimal)dr["往年净发货"] == 0 ? 0 : ((decimal)dr["本年净发货"] - (decimal)dr["往年净发货"]) / (decimal)dr["往年净发货"];
        dr["本年退货"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("本年退货")) return 0m;
            object value = dr["本年退货"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });
        dr["往年退货"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("往年退货")) return 0m;
            object value = dr["往年退货"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });
        dr["本年退货率"] = (decimal)dr["本年净发货"] == 0 ? 0 : (decimal)dr["本年退货"] / (decimal)dr["本年净发货"];
        dr["往年退货率"] = (decimal)dr["往年净发货"] == 0 ? 0 : (decimal)dr["往年退货"] / (decimal)dr["往年净发货"];
        dr["本年次数"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("本年次数")) return 0m;
            object value = dr["本年次数"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });
        dr["往年次数"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("往年次数")) return 0m;
            object value = dr["往年次数"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });
        dt.Rows.Add(dr);
        DataTableDynamicContext dataTable = new DataTableDynamicContext(dt);
        var ret = await TableExport.ExportExcelAsync(dataTable.GetItems(), DataTableDynamicContext.GetColumns(), $"产品渠道对应表_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 导出符合条件的所有数据 data
        // var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), DataTableDynamicContext.GetColumns(), $"客户对应产品_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
        ToastService.Success("提示", "导出成功");
    }

    private string OnSort(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContext.DataTable.Clone();

        DataView dv = DataTableDynamicContext.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();

        显示数据(sortTable);
        return sortString;
    }

    private decimal sum1;
    private decimal sum2;
    private decimal sum3;
    private decimal sum4;
    private decimal sum5;
    private decimal sum6;
    private decimal sum7;
    private decimal sum8;

    private async void Button_chaxun_Click(MouseEventArgs e)
    {
        sum1 = 0;
        sum2 = 0;
        sum3 = 0;
        sum4 = 0;
        sum5 = 0;
        sum6 = 0;
        sum7 = 0;
        sum8 = 0;
        //客户发货排名
        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd");
        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年 = 去年开始日期.Substring(0, 4);
        string 今年sql;
        string 去年sql;
        // string loginuser = username;
        string loginuser = "admin";
        sql = "SELECT DepartmentName FROM [netcoredev].[dbo].[View_PersonDepart] where  cPersonName='" + loginuser + "'";
        string depart = _SqlSugarScope.Ado.GetString(sql);

        string guid = Guid.NewGuid().ToString();  ///本次查询统一的
        if (string.IsNullOrEmpty(选中部门InStr) == true)
        {
            选中部门InStr += string.Join("','", View_DepartList.Select(it => it.cDepName));
        }
        string 部门清单 = "('" + 选中部门InStr + "')";

        sql = "select docyear, dbname FROM      Tset where docyear='" + 年度 + "'";
        DataTable tb1 = _SqlSugarScope.Ado.GetDataTable(sql);
        if (tb1.Rows.Count > 0)
        {
            今年sql = tb1.Rows[0]["dbname"].ToString();
            sql = "delete T客户对应产品 where usercode='" + loginuser + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //处理今年(今年sql)

            #region
            //1
            sql = @"insert into  T客户对应产品 (guid, usercode,  Depart, cCusCode, cDLCode, irowno,  cPersonCode,dDate,
                cInvCode,  cWhCode,本年发货,本年次数,本年发货数量,本年退盘,往年发货,往年次数,往年退盘,加减)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            sql += " cInvCode,cwhcode,iMoney,1,iQuantity,0,0,0,0,'加' from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            // sql += " and period='" + 年度 + "' and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "' and iMoney >0";
            sql += " and period='" + 年度 + "' and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //2
            sql = @"insert into  T客户对应产品 (guid, usercode,  Depart, cCusCode, cDLCode, irowno,  cPersonCode,dDate,
                cInvCode,  cWhCode,本年发货,本年次数,本年退货数量,本年退盘,往年发货,往年次数,往年退盘,加减)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            sql += " cInvCode,cwhcode,0,1,iQuantity,iMoney,0,0,0,'加' from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and period='" + 年度 + "' and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "' and iMoney <0 and cWhCode ='06'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //3
            // sql = @"insert into  T客户对应产品 (guid, usercode,  Depart, cCusCode, cDLCode, irowno,  cPersonCode,dDate,
            //     cInvCode,  cWhCode,本年发货,本年次数,退货数量,本年退盘,往年发货,往年次数,往年退盘,加减)";
            // sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            // sql += " cInvCode,cwhcode,abs(iMoney),1,iQuantity,iMoney,0,0,0,'减' from View_发票明细 where 1=1 ";
            // if (loginuser != "admin")
            // {
            //     sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
            //     sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            // }
            // sql += " and period='" + 年度 + "' and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "' and iMoney <0 and cWhCode='06'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            #region
            //1
            sql = @"insert into  T客户对应产品 (guid, usercode,  Depart, cCusCode, cDLCode, irowno,  cPersonCode,dDate,
                cInvCode,  cWhCode,本年发货,本年次数,往年发货数量,本年退盘,往年发货,往年次数,往年退盘,加减)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            sql += " cInvCode,cwhcode,0,0,iQuantity,0,iMoney,1,0,'加' from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            // sql += " and period='" + 去年 + "' and dDate<='" + 去年结束日期 + "' and dDate>='" + 去年开始日期 + "'  and iMoney >0";
            sql += " and period='" + 去年 + "' and dDate<='" + 去年结束日期 + "' and dDate>='" + 去年开始日期 + "' ";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //2
            sql = @"insert into  T客户对应产品 (guid, usercode,  Depart, cCusCode, cDLCode, irowno,  cPersonCode,dDate,
                cInvCode,  cWhCode,本年发货,本年次数,往年退货数量,本年退盘,往年发货,往年次数,往年退盘,加减)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            // sql += " cInvCode,cwhcode,0,0,0,0,iMoney,1,iMoney,'加' from View_发票明细 where 1=1 ";
            sql += " cInvCode,cwhcode,0,0,iQuantity,0,0,1,iMoney,'加' from View_发票明细 where 1=1 ";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            sql += " and period='" + 去年 + "' and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "' and iMoney <0 and cWhCode = '06'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //3
            // sql = @"insert into  T客户对应产品 (guid, usercode,  Depart, cCusCode, cDLCode, irowno,  cPersonCode,dDate,
            //     cInvCode,  cWhCode,本年发货,本年次数,退货数量,本年退盘,往年发货,往年次数,往年退盘,加减)";
            // sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,cDLCode,irowno,cPersonCode,dDate,";
            // // sql += " cInvCode,cwhcode,0,0,0,0,abs(iMoney),1,iMoney,'减' from View_发票明细 where 1=1 ";
            // sql += " cInvCode,cwhcode,0,0,0,0,0,1,iMoney,'减' from View_发票明细 where 1=1 ";
            // if (loginuser != "admin")
            // {
            //     sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
            //     sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            // }
            // sql += " and period='" + 去年 + "' and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "' and iMoney <0 and cWhCode='06'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion





            //更新客户
            sql = "update  a set a.代理商名称=b.cCusName ,a.销售员=b.cPersonName,a.cPersonCode=b.cCusPPerson ";
            sql += " from T客户对应产品 a, View_CustomPerson b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

            //更新部门
            sql = "update  a set a.部门=b.DepartmentName ";
            sql += " from T客户对应产品 a, View_PersonDepart b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

            //更新产品
            sql = "update  a set a.产品名称=b.cInvName,a.规格=b.cInvStd,a.类别=b.类别名称 ";
            sql += " from T客户对应产品 a, View_Inventory b where a.cInvCode=b.cInvCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

           
            //养殖类别
            sql = "update  a set a.养殖类别=b.养殖类别 ";
            sql += " from T客户对应产品 a, View_Custom b where  a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //显示数据
            switch (选中查询类别)
            {
                case "0":
                    {
                        sql = @"select  代理商名称,部门,销售员,产品名称,规格,类别,Convert(decimal(18,4),sum(本年发货)/10000) 本年发货,Convert(decimal(18,4),sum(往年发货)/10000) 往年发货,Convert(decimal(18,4),(sum(本年发货) -sum(往年发货))/10000)  差异额,
                CASE   WHEN sum(往年发货) > 0 THEN (sum(本年发货) -sum(往年发货))/sum(往年发货)
                  ELSE 0
                END AS 差异率,Convert(decimal(18,4),sum(本年退盘)/10000) 本年退盘,Convert(decimal(18,4),sum(往年退盘)/10000) 往年退盘,
                CASE   WHEN sum(本年发货) > 0 THEN sum(本年退盘)/sum(本年发货)
                  ELSE 0
                END AS 本年退货率,
                CASE   WHEN sum(往年发货) > 0 THEN sum(往年退盘)/sum(往年发货)
                  ELSE 0
                END AS 往年退货率,

                sum(本年次数) 本年次数 ,
                -- sum(退货数量) 退货数量,
                sum(往年次数) 往年次数
                FROM T客户对应产品 where guid='" + guid + "'  ";
                        break;
                    }
                default:
                    {
                        sql = @"select  代理商名称,部门,销售员,产品名称,规格,类别,sum(本年发货数量) 本年发货,sum(往年发货数量) 往年发货,sum(本年发货数量) -sum(往年发货数量)  差异额,
                            CASE   WHEN sum(往年发货数量) > 0 THEN (sum(本年发货数量) -sum(往年发货数量))/sum(往年发货数量)
                              ELSE 0
                            END AS 差异率,sum(本年退货数量) 本年退盘,sum(往年退货数量) 往年退盘,
                            CASE   WHEN sum(本年发货数量) > 0 THEN sum(本年退货数量)/sum(本年发货数量)
                              ELSE 0
                            END AS 本年退货率,
                            CASE   WHEN sum(往年发货数量) > 0 THEN sum(往年退货数量)/sum(往年发货数量)
                              ELSE 0
                            END AS 往年退货率,

                            sum(本年次数) 本年次数 ,
                            -- sum(退货数量) 退货数量,
                            sum(往年次数) 往年次数
                            FROM T客户对应产品 where guid='" + guid + "'  ";
                        break;
                    }
            }
                   
            if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
            {
                sql += " and 代理商名称 like '%" + this.选中客户.Text.Trim().Replace("-", "") + "%'";
            }
            sql += " and 部门 in " + 部门清单;

            if (选中产品InStr != "" && 选中产品InStr != "请选择")
            {
                sql += " and 产品名称 in ('" + 选中产品InStr + "')";
            }

            if (选中业务员InStr != "")
            {
                sql += " and 销售员 in ('" + 选中业务员InStr + "')";
            }

            if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text != "非小棚虾")
            {
                sql += " and 养殖类别='" + this.选中养殖类别.Text + "'";
            }
            if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text == "非小棚虾")
            {
                sql += " and 养殖类别 is null";
            }

            sql += " group by 代理商名称,部门, 销售员,产品名称,规格,类别 ";
            DataTable dataTableHZ = _SqlSugarScope.Ado.GetDataTable(sql);

            sql = " delete T客户对应产品 where  guid='" + guid + "' ";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
            显示数据(dataTableHZ);
            await ToastService.Information("提示", "查询成功");
        }
        else
        {
            await ToastService.Success("提示", "选择的日期有误", autoHide: true);
        }

    }

    public void 显示数据(DataTable dt)
    {
        //  UserDataAuth userDataAuth = _SqlSugarScope.Queryable<UserDataAuth>().Where(it => it.UserName == username).First();
        // List<Guid> guids = userDataAuth.CanViewDepart.Split(",").ToList().Select(it => Guid.Parse(it)).ToList();
        // List<Sys_Department> depts = _SqlSugarScope.Queryable<Sys_Department>().Where(it => guids.Contains(it.DepartmentId)).ToList();
        // List<string> deptNames = depts.Select(it => it.DepartmentName).ToList();

        string a = 选中查询列;
        if (dt != null && dt.Rows.Count > 0)
        {
            //修改列名
            if (dt.Columns.Contains("代理商名称"))
            {
                dt.Columns["代理商名称"].ColumnName = "客户名称";
            }
            if (dt.Columns.Contains("销售员"))
            {
                dt.Columns["销售员"].ColumnName = "业务员";
            }
            if (dt.Columns.Contains("本年发货"))
            {
                dt.Columns["本年发货"].ColumnName = "本年净发货";
            }
            if (dt.Columns.Contains("往年发货"))
            {
                dt.Columns["往年发货"].ColumnName = "往年净发货";
            }

            if (dt.Columns.Contains("本年退盘"))
            {
                dt.Columns["本年退盘"].ColumnName = "本年退货";
            }

            if (dt.Columns.Contains("往年退盘"))
            {
                dt.Columns["往年退盘"].ColumnName = "往年退货";
            }

            //找出对应选中的销售员
            // if (选中业务员InStr != "")
            // {
            //     var rows = dt.Select("业务员 in ('" + 选中业务员InStr + "')");
            //     if (rows != null && rows.Length > 0)
            //     {
            //         dt = rows.CopyToDataTable();
            //     }
            //     else
            //     {
            //         dt.Rows.Clear();    
            //     }
            //     // dt = dt.Select("销售员 in ('" + 选中业务员InStr + "')")?.CopyToDataTable();
            // }
            // if (选中产品InStr != "" && 选中产品InStr != "请选择")
            // {
            //     var rows = dt.Select("产品名称 in ('" + 选中产品InStr + "')");
            //     if (rows != null && rows.Length > 0)
            //     {
            //         dt = rows.CopyToDataTable();
            //     }
            //     else
            //     {
            //         dt.Rows.Clear();
            //     }
            // }
            //如果选中了查询类别，根据查询类别选中项进行分组
           
            if (选中查询列 != null && string.IsNullOrEmpty(选中查询列.Replace("金额", "").Replace("数量", "")) == false)
            {
                string arr = 选中查询列.Replace("金额", "").Replace("数量", "");
                //arr 去除 金额  数量

                string groupby = "";
                var groupedQuery = dt.AsEnumerable().GroupBy(row => new
                {
                    AgentName = arr.Contains("客户名称") ? row.Field<string>("客户名称") : null,
                    Department = arr.Contains("部门") ? row.Field<string>("部门") : null,
                    SalesPerson = arr.Contains("业务员") ? row.Field<string>("业务员") : null,
                    ProductName = arr.Contains("产品名称") ? row.Field<string>("产品名称") : null,
                    Category = arr.Contains("类别") ? row.Field<string>("类别") : null,
                })
                   .Select(group => new
                   {
                       Key = group.Key,
                       本年净发货 = group.Sum(r => r.Field<decimal?>("本年净发货")),
                       往年净发货 = group.Sum(r => r.Field<decimal?>("往年净发货")),
                       差异额 = group.Sum(r => r.Field<decimal?>("本年净发货")) - group.Sum(r => r.Field<decimal?>("往年净发货")),
                       差异率 = group.Sum(r => r.Field<decimal?>("往年净发货")) > 0 ? (group.Sum(r => r.Field<decimal?>("本年净发货")) - group.Sum(r => r.Field<decimal?>("往年净发货"))) / group.Sum(r => r.Field<decimal?>("往年净发货")) : 0,
                       本年退货 = group.Sum(r => r.Field<decimal?>("本年退货")),
                       往年退货 = group.Sum(r => r.Field<decimal?>("往年退货")),
                       本年退货率 = group.Sum(r => r.Field<decimal?>("本年净发货")) > 0 ? (group.Sum(r => r.Field<decimal?>("本年退货"))) / group.Sum(r => r.Field<decimal?>("本年净发货")) : 0,
                       往年退货率 = group.Sum(r => r.Field<decimal?>("往年净发货")) > 0 ? (group.Sum(r => r.Field<decimal?>("往年退货"))) / group.Sum(r => r.Field<decimal?>("往年净发货")) : 0,
                       本年次数 = group.Sum(r => r.Field<decimal?>("本年次数")),
                      
                       
                       往年次数 = group.Sum(r => r.Field<decimal?>("往年次数")),
                       
                      
                   });
                // 显示结果
                // 创建结果DataTable
                dt = new DataTable();
                if (arr.Contains("客户名称")) dt.Columns.Add("客户名称", typeof(string));
                if (arr.Contains("部门")) dt.Columns.Add("部门", typeof(string));
                if (arr.Contains("业务员")) dt.Columns.Add("业务员", typeof(string));
                if (arr.Contains("产品名称")) dt.Columns.Add("产品名称", typeof(string));
                if (arr.Contains("类别")) dt.Columns.Add("类别", typeof(string));
                dt.Columns.Add("本年净发货", typeof(decimal));
                dt.Columns.Add("往年净发货", typeof(decimal));
                dt.Columns.Add("差异额", typeof(decimal));
                dt.Columns.Add("差异率", typeof(decimal));
                dt.Columns.Add("本年退货", typeof(decimal));
                dt.Columns.Add("往年退货", typeof(decimal));
              
                dt.Columns.Add("本年退货率", typeof(decimal));
                dt.Columns.Add("往年退货率", typeof(decimal));

                dt.Columns.Add("本年次数", typeof(decimal));
                dt.Columns.Add("往年次数", typeof(decimal));


                // 填充结果DataTable
                foreach (var group in groupedQuery)
                {
                    DataRow row = dt.NewRow();
                    if (arr.Contains("客户名称")) row["客户名称"] = group.Key.AgentName;
                    if (arr.Contains("部门")) row["部门"] = group.Key.Department;
                    if (arr.Contains("业务员")) row["业务员"] = group.Key.SalesPerson;
                    if (arr.Contains("产品名称")) row["产品名称"] = group.Key.ProductName;
                    if (arr.Contains("类别")) row["类别"] = group.Key.Category;
                    row["本年净发货"] = group.本年净发货;
                    row["往年净发货"] = group.往年净发货;
                    row["差异额"] = group.差异额;
                    row["差异率"] = group.差异率;
                    row["本年退货"] = group.本年退货;
                    row["往年退货"] = group.往年退货;
                    row["本年退货率"] = group.本年退货率;
                    row["往年退货率"] = group.往年退货率;

                    row["本年次数"] = group.本年次数;
                    row["往年次数"] = group.往年次数;
                    
                   
                    dt.Rows.Add(row);
                }
            }
            // if (选中查询列 != null)
            // {
            //     if (选中查询列.Split(",").Contains("金额") == true && 选中查询列.Split(",").Contains("数量") == false)
            //     {
            //         if (dt != null && dt.Columns.Contains("本年次数"))
            //         {
            //             dt.Columns.Remove("本年次数");
            //         }
            //         if (dt != null && dt.Columns.Contains("往年次数"))
            //         {
            //             dt.Columns.Remove("往年次数");
            //         }
            //     }
            //     if (选中查询列.Split(",").Contains("金额") == false && 选中查询列.Split(",").Contains("数量") == true)
            //     {
            //         if (dt != null && dt.Columns.Contains("本年发货"))
            //         {
            //             dt.Columns.Remove("本年发货");
            //         }
            //         if (dt != null && dt.Columns.Contains("往年发货"))
            //         {
            //             dt.Columns.Remove("往年发货");
            //         }
            //         if (dt != null && dt.Columns.Contains("差异额"))
            //         {
            //             dt.Columns.Remove("差异额");
            //         }
            //     }
            // }
            //dt第一列插入序号列
            if (dt.Columns.Contains("序号") == false)
            {
                dt.Columns.Add("序号", typeof(int)).SetOrdinal(0);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["序号"] = i + 1;
            }
        }
        DataTableDynamicContext = new DataTableDynamicContext(dt, (context, col) =>
            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
                if (col.GetFieldName() == "业务员")
                {
                    col.Width = 60;
                }
                else if (col.GetFieldName() == "序号")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "类别")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "客户名称")
                {
                    col.Width = 120;
                    // col.TextEllipsis = true;
                }
                else if (col.GetFieldName() == "本年次数")
                {
                    col.Width = 70;
                    col.TextEllipsis = true;
                    col.FormatString = "f0";
                }
                else if (col.GetFieldName() == "往年次数")
                {
                    col.Width = 70;
                    col.TextEllipsis = true;
                    col.FormatString = "f0";
                }
                else
                {
                    col.Width = 80;
                }
                // col.TextWrap = true;
                // //如果列的类型是数字类型设置宽度120
                // if (Type.GetTypeCode(col.PropertyType) != TypeCode.String)
                // {
                //     col.Width = 120;
                // }
                // else
                // {
                //     col.Width = 150;
                // }
                if (col.GetFieldName() == "本期回款率" || col.GetFieldName() == "同期回款率" || col.GetFieldName() == "差异率" || col.GetFieldName() == "本年退货率" || col.GetFieldName() == "往年退货率")
                {
                    col.FormatString = "P2";
                }
            });

        StateHasChanged();
    }
}
